<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>触发器 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/104.895c9e53.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/100.245d2d1d.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/103.b0e7acf6.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/93.bbc81ca6.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/95.f7232d68.js"><link rel="prefetch" href="/noteslibrary/assets/js/96.440e6bbe.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/98.e9a01f98.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/fe941e/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/fe941e/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/fe941e/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/fe941e/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/fe941e/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/fe941e/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" class="sidebar-link">多表查询</a></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" class="sidebar-link">聚合函数</a></li><li><a href="/noteslibrary/pages/3ea5ac/" class="sidebar-link">子查询</a></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" class="sidebar-link">数据处理之增删改</a></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" class="sidebar-link">约束</a></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" class="sidebar-link">变量与流程控制与游标</a></li><li><a href="/noteslibrary/pages/fe941e/" aria-current="page" class="active sidebar-link">触发器</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/fe941e/#_1-触发器概述" class="sidebar-link">1. 触发器概述</a></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/fe941e/#_2-触发器的创建" class="sidebar-link">2. 触发器的创建</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_2-1-创建触发器语法" class="sidebar-link">2.1 创建触发器语法</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_2-2-代码举例" class="sidebar-link">2.2 代码举例</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/fe941e/#_3-查看、删除触发器" class="sidebar-link">3. 查看、删除触发器</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_3-1-查看触发器" class="sidebar-link">3.1 查看触发器</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_3-2-删除触发器" class="sidebar-link">3.2 删除触发器</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/fe941e/#_4-触发器的优缺点" class="sidebar-link">4. 触发器的优缺点</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_4-1-优点" class="sidebar-link">4.1 优点</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_4-2-缺点" class="sidebar-link">4.2 缺点</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/fe941e/#_4-3-注意点" class="sidebar-link">4.3 注意点</a></li></ul></li></ul></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">触发器<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="触发器"><a href="#触发器" class="header-anchor">#</a> 触发器</h1> <p>​	在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 <code>商品信息</code> 和 <code>库存信息</code> 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。</p> <p>这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用 <code>事务</code> 包裹起来，确保这两个操作成为一个 <code>原子操作</code> ，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很 <code>容易忘记其中的一步</code> ，导致数据缺失。
这个时候，咱们可以使用触发器。<strong>你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作</strong>。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。</p> <h2 id="_1-触发器概述"><a href="#_1-触发器概述" class="header-anchor">#</a> 1. 触发器概述</h2> <p>MySQL从 <code>5.0.2</code> 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。</p> <p>触发器是由 <code>事件来触发</code> 某个操作，这些事件包括 <code>INSERT</code> 、 <code>UPDATE</code> 、 <code>DELETE</code> 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 <code>自动</code> 激发触发器执行相应的操作。</p> <p>当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。</p> <h2 id="_2-触发器的创建"><a href="#_2-触发器的创建" class="header-anchor">#</a> 2. 触发器的创建</h2> <h3 id="_2-1-创建触发器语法"><a href="#_2-1-创建触发器语法" class="header-anchor">#</a> 2.1 创建触发器语法</h3> <p>创建触发器的语法结构是：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> 触发器名称
{BEFORE<span class="token operator">|</span><span class="token keyword">AFTER</span>} {<span class="token keyword">INSERT</span><span class="token operator">|</span><span class="token keyword">UPDATE</span><span class="token operator">|</span><span class="token keyword">DELETE</span>} <span class="token keyword">ON</span> 表名
<span class="token keyword">FOR EACH ROW</span>
触发器执行的语句块<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>说明：</p> <ul><li><code>表名</code> ：表示触发器监控的对象。</li> <li><code>BEFORE|AFTER</code> ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。</li> <li><code>INSERT|UPDATE|DELETE</code> ：表示触发的事件。
<ul><li>INSERT 表示插入记录时触发；</li> <li>UPDATE 表示更新记录时触发；</li> <li>DELETE 表示删除记录时触发。</li></ul></li> <li><code>触发器执行的语句块</code> ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。</li></ul> <h3 id="_2-2-代码举例"><a href="#_2-2-代码举例" class="header-anchor">#</a> 2.2 代码举例</h3> <p><strong>举例1</strong>：
1、创建数据表：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test_trigger <span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    t_note <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test_trigger_log <span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    t_log <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>2、创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> 
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> before_insert
BEFORE <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> test_trigger
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">BEGIN</span>
	<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test_trigger_log <span class="token punctuation">(</span>t_log<span class="token punctuation">)</span>
	<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'before_insert'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> 
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>3、向test_trigger数据表中插入数据</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test_trigger <span class="token punctuation">(</span>t_note<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'测试 BEFORE INSERT 触发器'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>4、查看test_trigger_log数据表中的数据</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251930782.png" alt="image-20220316145607825"></p> <p><strong>举例2</strong>：
1、创建名称为after_insert的触发器，向test_trigger数据表插入数据之后，向test_trigger_log数据表中插入after_insert的日志信息。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> after_insert
<span class="token keyword">AFTER</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> test_trigger
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">BEGIN</span>
	<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test_trigger_log <span class="token punctuation">(</span>t_log<span class="token punctuation">)</span>
	<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'after_insert'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> 
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p>2、向test_trigger数据表中插入数据。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test_trigger <span class="token punctuation">(</span>t_note<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'测试 AFTER INSERT 触发器'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>3、查看test_trigger_log数据表中的数据</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test_trigger_log<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">----+---------------+</span>
<span class="token operator">|</span> id <span class="token operator">|</span> t_log         <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+---------------+</span>
<span class="token operator">|</span>  <span class="token number">1</span> <span class="token operator">|</span> before_insert <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">2</span> <span class="token operator">|</span> before_insert <span class="token operator">|</span>
<span class="token operator">|</span>  <span class="token number">3</span> <span class="token operator">|</span> after_insert  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">----+---------------+</span>
<span class="token number">3</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p><strong>举例3</strong>：定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为'HY000'的错误，从而使得添加失败。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> salary_check_trigger
BEFORE <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> employees <span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">BEGIN</span>
	<span class="token keyword">DECLARE</span> mgrsalary <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
	<span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> mgrsalary <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> NEW<span class="token punctuation">.</span>manager_id<span class="token punctuation">;</span>
	
	<span class="token keyword">IF</span> NEW<span class="token punctuation">.</span>salary <span class="token operator">&gt;</span> mgrsalary <span class="token keyword">THEN</span>
		SIGNAL SQLSTATE <span class="token string">'HY000'</span> <span class="token keyword">SET</span> MESSAGE_TEXT <span class="token operator">=</span> <span class="token string">'薪资高于领导薪资错误'</span><span class="token punctuation">;</span>
	<span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><p>上面触发器声明过程中的NEW关键字代表INSERT添加语句的新记录。</p> <h2 id="_3-查看、删除触发器"><a href="#_3-查看、删除触发器" class="header-anchor">#</a> 3. 查看、删除触发器</h2> <h3 id="_3-1-查看触发器"><a href="#_3-1-查看触发器" class="header-anchor">#</a> 3.1 查看触发器</h3> <p>查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。</p> <p>方式1：查看当前数据库的所有触发器的定义</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> TRIGGERS\G
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>方式2：查看当前数据库中某个触发器的定义</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> 触发器名
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>TRIGGERS<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_3-2-删除触发器"><a href="#_3-2-删除触发器" class="header-anchor">#</a> 3.2 删除触发器</h3> <p>触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">TRIGGER</span>  <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> 触发器名称<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="_4-触发器的优缺点"><a href="#_4-触发器的优缺点" class="header-anchor">#</a> 4. 触发器的优缺点</h2> <h3 id="_4-1-优点"><a href="#_4-1-优点" class="header-anchor">#</a> 4.1 优点</h3> <p><strong>1、触发器可以确保数据的完整性</strong>。
假设我们用 <code>进货单头表</code> （demo.importhead）来保存进货单的总体信息，包括进货单编号、供货商编号、仓库编号、总计进货数量、总计进货金额和验收日期。</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251930783.png" alt="image-20220316145813005"></p> <p>用 <code>进货单明细表</code> （demo.importdetails）来保存进货商品的明细，包括进货单编号、商品编号、进货数量、进货价格和进货金额。</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251930784.png" alt="image-20220316145823495"></p> <p>额就不等于进货单明细表中数量合计和金额合计了，这就是数据不一致。
为了解决这个问题，我们就可以使用触发器，<strong>规定每当进货单明细表有数据插入、修改和删除的操作时，自动触发 2 步操作：</strong></p> <p>1）重新计算进货单明细表中的数量合计和金额合计；</p> <p>2）用第一步中计算出来的值更新进货单头表中的合计数量与合计金额。</p> <p>这样一来，进货单头表中的合计数量与合计金额的值，就始终与进货单明细表中计算出来的合计数量与合计金额的值相同，数据就是一致的，不会互相矛盾。</p> <p><strong>2、触发器可以帮助我们记录操作日志。</strong>
利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助。</p> <p><strong>3、触发器还可以用在操作数据前，对数据进行合法性检查。</strong>
比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏……</p> <p>这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统。</p> <h3 id="_4-2-缺点"><a href="#_4-2-缺点" class="header-anchor">#</a> 4.2 缺点</h3> <p><strong>1、触发器最大的一个问题就是可读性差。</strong>
因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能 <code>不受应用层的控制</code> 。这对系统维护是非常有挑战的。</p> <p>比如，创建触发器用于修改会员储值操作。如果触发器中的操作出了问题，会导致会员储值金额更新失败。我用下面的代码演示一下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">update</span> demo<span class="token punctuation">.</span>membermaster <span class="token keyword">set</span> memberdeposit<span class="token operator">=</span><span class="token number">20</span> <span class="token keyword">where</span> memberid <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
ERROR <span class="token number">1054</span> <span class="token punctuation">(</span><span class="token number">42</span>S22<span class="token punctuation">)</span>: Unknown <span class="token keyword">column</span> <span class="token string">'aa'</span> <span class="token operator">in</span> <span class="token string">'field list'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>结果显示，系统提示错误，字段“aa”不存在。
这是因为，触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是会员信息表的结构出了问题。说不定你还会给会员信息表添加一个叫“aa”的字段，试图解决这个问题，结果只能是白费力。</p> <p><strong>2、相关数据的变更，可能会导致触发器出错。</strong>
特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。</p> <h3 id="_4-3-注意点"><a href="#_4-3-注意点" class="header-anchor">#</a> 4.3 注意点</h3> <p>注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。</p> <p>例如：基于子表员工表（t_employee）的DELETE语句定义了触发器t1，而子表的部门编号（did）字段定义了外键约束引用了父表部门表（t_department）的主键列部门编号（did），并且该外键加了“ON DELETE SET NULL”子句，那么如果此时删除父表部门表（t_department）在子表员工表（t_employee）有匹配记录的部门记录时，会引起子表员工表（t_employee）匹配记录的部门编号（did）修改为NULL，但是此时不会激活触发器t1。只有直接对子表员工表（t_employee）执行DELETE语句时才会激活触发器t1。</p></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/17.触发器.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/f68b46/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">变量与流程控制与游标</div></a> <a href="/noteslibrary/pages/efad7b/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">MySQL8其它新特性</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/f68b46/" class="prev">变量与流程控制与游标</a></span> <span class="next"><a href="/noteslibrary/pages/efad7b/">MySQL8其它新特性</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/104.895c9e53.js" defer></script>
  </body>
</html>
